-- MindMate Database Schema
-- 智能AI学习伙伴系统数据库设计

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable updated_at trigger function
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- 1. Users Table (用户表)
-- =====================================================
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50),
    avatar_url TEXT,
    preferences JSONB DEFAULT '{
        "theme": "system",
        "language": "zh-CN",
        "defaultModel": "gpt-3.5-turbo",
        "searchEnabled": true,
        "contextThreshold": 15
    }'::JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for users table
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_users
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 2. Roles Table (角色表)
-- =====================================================
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE, -- NULL表示系统预设角色
    name VARCHAR(100) NOT NULL,
    description TEXT,
    system_prompt TEXT NOT NULL,
    model_preferences JSONB DEFAULT '{
        "preferredModel": "gpt-3.5-turbo",
        "temperature": 0.7,
        "max_tokens": 1500,
        "top_p": 0.9
    }'::JSONB,
    is_system BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for roles table
CREATE INDEX idx_roles_user ON roles(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_roles_system ON roles(is_system) WHERE is_system = TRUE;
CREATE INDEX idx_roles_active ON roles(is_active) WHERE is_active = TRUE;

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_roles
    BEFORE UPDATE ON roles
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 3. Sessions Table (会话表)
-- =====================================================
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL DEFAULT '新对话',
    model_config JSONB DEFAULT '{
        "model": "gpt-3.5-turbo",
        "temperature": 0.7,
        "max_tokens": 1500,
        "top_p": 0.9,
        "frequency_penalty": 0.0,
        "presence_penalty": 0.0
    }'::JSONB,
    role_id UUID REFERENCES roles(id) ON DELETE SET NULL,
    settings JSONB DEFAULT '{
        "searchEnabled": true,
        "contextThreshold": 15,
        "autoSummary": true
    }'::JSONB,
    is_archived BOOLEAN DEFAULT FALSE,
    is_pinned BOOLEAN DEFAULT FALSE,
    message_count INTEGER DEFAULT 0,
    last_message_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for sessions table
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_created_at ON sessions(created_at DESC);
CREATE INDEX idx_sessions_last_message ON sessions(last_message_at DESC);
CREATE INDEX idx_sessions_user_active ON sessions(user_id, is_archived) WHERE NOT is_archived;
CREATE INDEX idx_sessions_user_pinned ON sessions(user_id, is_pinned) WHERE is_pinned = TRUE;

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_sessions
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 4. Messages Table (消息表)
-- =====================================================
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    branch_id UUID DEFAULT NULL, -- NULL表示主分支
    parent_message_id UUID REFERENCES messages(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::JSONB, -- 模型名、token数、搜索结果等
    is_pinned BOOLEAN DEFAULT FALSE, -- 是否固定不被压缩
    is_edited BOOLEAN DEFAULT FALSE, -- 是否被编辑过
    is_compressed BOOLEAN DEFAULT FALSE, -- 是否已被压缩到备忘录
    sequence_number INTEGER NOT NULL, -- 在分支中的序号
    token_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for messages table
CREATE INDEX idx_messages_session_id ON messages(session_id);
CREATE INDEX idx_messages_branch_sequence ON messages(session_id, branch_id, sequence_number);
CREATE INDEX idx_messages_parent ON messages(parent_message_id);
CREATE INDEX idx_messages_created_at ON messages(created_at DESC);
CREATE INDEX idx_messages_pinned ON messages(session_id, is_pinned) WHERE is_pinned = TRUE;
CREATE INDEX idx_messages_compressed ON messages(session_id, is_compressed) WHERE is_compressed = FALSE;

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_messages
    BEFORE UPDATE ON messages
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 5. Memos Table (备忘录表)
-- =====================================================
CREATE TABLE memos (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    branch_id UUID DEFAULT NULL,
    title VARCHAR(200) DEFAULT '对话摘要',
    content TEXT NOT NULL, -- 备忘录内容
    covered_range JSONB NOT NULL, -- 覆盖的消息范围
    covered_message_count INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE, -- 是否启用
    generated_by VARCHAR(50) DEFAULT 'system', -- 生成方式: system, user
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for memos table
CREATE INDEX idx_memos_session ON memos(session_id, is_active);
CREATE INDEX idx_memos_branch ON memos(session_id, branch_id) WHERE branch_id IS NOT NULL;
CREATE INDEX idx_memos_created_at ON memos(created_at DESC);

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_memos
    BEFORE UPDATE ON memos
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 6. Search Logs Table (搜索日志表)
-- =====================================================
CREATE TABLE search_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    message_id UUID REFERENCES messages(id) ON DELETE CASCADE,
    query TEXT NOT NULL,
    results JSONB, -- 搜索结果
    result_count INTEGER DEFAULT 0,
    provider VARCHAR(50) DEFAULT 'duckduckgo',
    status VARCHAR(20) DEFAULT 'success' CHECK (status IN ('success', 'failed', 'timeout')),
    response_time INTEGER DEFAULT 0, -- 响应时间(ms)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for search_logs table
CREATE INDEX idx_search_logs_session ON search_logs(session_id);
CREATE INDEX idx_search_logs_message ON search_logs(message_id);
CREATE INDEX idx_search_logs_created_at ON search_logs(created_at DESC);
CREATE INDEX idx_search_logs_provider ON search_logs(provider);

-- =====================================================
-- 7. System Configs Table (系统配置表)
-- =====================================================
CREATE TABLE system_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR(100) UNIQUE NOT NULL,
    value JSONB NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for system_configs table
CREATE INDEX idx_system_configs_key ON system_configs(key);
CREATE INDEX idx_system_configs_active ON system_configs(is_active) WHERE is_active = TRUE;

-- Trigger for updated_at
CREATE TRIGGER set_timestamp_system_configs
    BEFORE UPDATE ON system_configs
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();

-- =====================================================
-- 初始化系统数据
-- =====================================================

-- 插入系统预设角色
INSERT INTO roles (id, name, description, system_prompt, model_preferences, is_system) VALUES
    (
        uuid_generate_v4(),
        '通用助手',
        '友善、专业的AI助手，能够协助各种任务',
        '你是一个友善、专业的AI助手。请以温暖、耐心的语调回答用户问题，提供准确、有用的信息。在回答时要考虑用户的感受，给予鼓励和支持。',
        '{
            "preferredModel": "gpt-3.5-turbo",
            "temperature": 0.7,
            "max_tokens": 1500,
            "top_p": 0.9
        }'::JSONB,
        TRUE
    ),
    (
        uuid_generate_v4(),
        '学习导师',
        '专注于教学指导的AI导师，善于循循善诱',
        '你是一位经验丰富的学习导师。请以循循善诱的方式帮助用户学习，不要直接给出答案，而是通过提问和引导让用户自己思考和发现。要有耐心，多给予鼓励和正面反馈。',
        '{
            "preferredModel": "gpt-3.5-turbo",
            "temperature": 0.6,
            "max_tokens": 2000,
            "top_p": 0.85
        }'::JSONB,
        TRUE
    ),
    (
        uuid_generate_v4(),
        '创意伙伴',
        '充满创意和想象力的AI伙伴，擅长头脑风暴',
        '你是一个充满创意和想象力的AI伙伴。请以开放、活跃的思维帮助用户进行创意思考和头脑风暴。要勇于提出新颖的想法，鼓励用户跳出固有思维模式。',
        '{
            "preferredModel": "gpt-3.5-turbo",
            "temperature": 0.9,
            "max_tokens": 2000,
            "top_p": 0.95
        }'::JSONB,
        TRUE
    );

-- 插入系统配置
INSERT INTO system_configs (key, value, description) VALUES
    ('available_models', '[
        {
            "id": "gpt-3.5-turbo",
            "name": "GPT-3.5 Turbo",
            "provider": "openai",
            "contextLimit": 4096,
            "costPer1kTokens": 0.002,
            "capabilities": ["chat", "analysis"]
        },
        {
            "id": "gpt-4",
            "name": "GPT-4",
            "provider": "openai", 
            "contextLimit": 8192,
            "costPer1kTokens": 0.03,
            "capabilities": ["chat", "analysis", "coding", "reasoning"]
        },
        {
            "id": "claude-3-sonnet",
            "name": "Claude 3 Sonnet",
            "provider": "anthropic",
            "contextLimit": 200000,
            "costPer1kTokens": 0.015,
            "capabilities": ["chat", "analysis", "long_context", "coding"]
        }
    ]'::JSONB, '可用的AI模型列表'),
    ('rate_limits', '{
        "requestsPerMinute": 60,
        "tokensPerHour": 10000,
        "searchPerHour": 50
    }'::JSONB, '系统限流配置'),
    ('features', '{
        "searchEnabled": true,
        "memosEnabled": true,
        "branchingEnabled": true,
        "roleCustomization": true
    }'::JSONB, '系统功能开关');

-- =====================================================
-- 数据库视图和函数
-- =====================================================

-- 创建会话统计视图
CREATE VIEW session_stats AS
SELECT 
    s.id,
    s.user_id,
    s.title,
    s.created_at,
    s.last_message_at,
    COUNT(m.id) as message_count,
    COUNT(CASE WHEN m.role = 'user' THEN 1 END) as user_message_count,
    COUNT(CASE WHEN m.role = 'assistant' THEN 1 END) as assistant_message_count,
    SUM(m.token_count) as total_tokens
FROM sessions s
LEFT JOIN messages m ON s.id = m.session_id AND m.is_compressed = FALSE
GROUP BY s.id, s.user_id, s.title, s.created_at, s.last_message_at;

-- 创建用户统计视图
CREATE VIEW user_stats AS
SELECT 
    u.id,
    u.email,
    u.username,
    u.created_at,
    COUNT(DISTINCT s.id) as session_count,
    COUNT(DISTINCT m.id) as message_count,
    SUM(m.token_count) as total_tokens,
    COUNT(DISTINCT sl.id) as search_count
FROM users u
LEFT JOIN sessions s ON u.id = s.user_id
LEFT JOIN messages m ON s.id = m.session_id
LEFT JOIN search_logs sl ON s.id = sl.session_id
GROUP BY u.id, u.email, u.username, u.created_at;

-- 创建获取对话上下文的函数
CREATE OR REPLACE FUNCTION get_session_context(
    session_uuid UUID,
    branch_uuid UUID DEFAULT NULL,
    max_messages INTEGER DEFAULT 10
)
RETURNS TABLE (
    id UUID,
    role VARCHAR,
    content TEXT,
    sequence_number INTEGER,
    created_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        m.id,
        m.role,
        m.content,
        m.sequence_number,
        m.created_at
    FROM messages m
    WHERE m.session_id = session_uuid
        AND (branch_uuid IS NULL OR m.branch_id = branch_uuid OR m.branch_id IS NULL)
        AND m.is_compressed = FALSE
    ORDER BY m.sequence_number DESC
    LIMIT max_messages;
END;
$$ LANGUAGE plpgsql;

-- 创建更新会话最后消息时间的函数
CREATE OR REPLACE FUNCTION update_session_last_message()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE sessions 
    SET 
        last_message_at = NEW.created_at,
        message_count = (
            SELECT COUNT(*) 
            FROM messages 
            WHERE session_id = NEW.session_id 
            AND is_compressed = FALSE
        )
    WHERE id = NEW.session_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建消息插入触发器
CREATE TRIGGER update_session_on_message_insert
    AFTER INSERT ON messages
    FOR EACH ROW
    EXECUTE FUNCTION update_session_last_message();

-- =====================================================
-- 性能优化和约束
-- =====================================================

-- 添加表空间和分区（如果需要）
-- 这里预留了分区表的可能性，用于大量数据的场景

-- 添加额外的约束
ALTER TABLE messages ADD CONSTRAINT chk_sequence_number_positive 
    CHECK (sequence_number >= 0);

ALTER TABLE memos ADD CONSTRAINT chk_covered_message_count_positive 
    CHECK (covered_message_count >= 0);

ALTER TABLE search_logs ADD CONSTRAINT chk_response_time_positive 
    CHECK (response_time >= 0);

-- 创建复合索引优化查询性能
CREATE INDEX idx_messages_session_role_seq ON messages(session_id, role, sequence_number);
CREATE INDEX idx_messages_session_compressed ON messages(session_id, is_compressed, sequence_number);
CREATE INDEX idx_sessions_user_updated ON sessions(user_id, updated_at DESC);

-- =====================================================
-- 备注和文档
-- =====================================================

COMMENT ON TABLE users IS '用户信息表，存储用户基本信息和偏好设置';
COMMENT ON TABLE roles IS '角色表，包含系统预设角色和用户自定义角色';
COMMENT ON TABLE sessions IS '会话表，每个对话会话的基本信息';
COMMENT ON TABLE messages IS '消息表，存储所有对话消息，支持分支结构';
COMMENT ON TABLE memos IS '备忘录表，存储自动生成的对话摘要';
COMMENT ON TABLE search_logs IS '搜索日志表，记录所有搜索请求和结果';
COMMENT ON TABLE system_configs IS '系统配置表，存储全局配置信息';

COMMENT ON COLUMN messages.branch_id IS '分支ID，NULL表示主分支，用于支持对话分支功能';
COMMENT ON COLUMN messages.is_pinned IS '固定标记，固定的消息不会被压缩到备忘录';
COMMENT ON COLUMN messages.is_compressed IS '压缩标记，已被压缩到备忘录的消息';
COMMENT ON COLUMN memos.covered_range IS 'JSON格式，记录备忘录覆盖的消息范围';

-- 数据库初始化完成
-- MindMate Database Schema v1.0